﻿using DataAccess.DBConnections;
using DataAccess.ProxyConnections;
using Oracle.DataAccess.Client;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccess.Ejip
{
    public class AuthenConnection
    {
        DBConnectionsEngine dbEngine;
        OracleConnection Conn;
        Hashtable hashtable;
        string strSql;

        public AuthenConnection() 
        { 
            
        }
        public DataTable getAppTokenData(string token)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@" 
            SELECT APPTOKEN,APPLNO,APPLNAME,EMPNO,PASSWORD FROM APPTOKEN WHERE TOKEN= :a ");
            hashtable = new Hashtable();
            hashtable.Add(":a", token);

            return  dbEngine.GetDataTable(strSql,hashtable,Conn);
        }
        public int DeleteToken(int pAppTokenId)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = " DELETE FROM APPTOKEN WHERE APPTOKEN=" + (object) pAppTokenId;
            return dbEngine.ExecuteNonQuery(strSql, Conn);

        }
        public DataTable getUserData(string pUser, string pApplNo)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@"
            SELECT d.DESCRIPTION,c.GROUPDESC,a.EMPNO,a.EMPNAME,a.PASSWORD,
            b.GROUPNO,a.DIVISION,b.STORENO,a.TEL,a.EMAIL,
            a.STATUS,b.APPLNO,a.EMPID ,b.PROFILELEVEL,a.LANG
            FROM USER_PROFILE a,
            USER_PROFILE_GROUP b,
            FUNCTION_GROUP c,
            APPLICATION d 
            WHERE a.EMPNO=b.EMPNO AND b.APPLNO=c.APPLNO 
            AND b.GROUPNO=c.GROUPNO AND c.APPLNO=d.APPLNO 
            AND LOWER(a.EMPNO)=  :a 
            AND b.APPLNO= :b ");

            hashtable = new Hashtable();
            hashtable.Add(":a", pUser);
            hashtable.Add(":b", pApplNo);


            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }
        public DataTable getFunctionApplication(string pUsername, string pApplNo)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@"
            SELECT d.FUNCNO,d.FUNCNAME,d.PARENTFUNC,d.MAINSCREEN,
            nvl(d.SEQMENU,0)as SEQMENU,nvl(d.SEQSUBMENU1,0)as SEQSUBMENU1,nvl(d.SEQSUBMENU2,0) as SEQSUBMENU2
            FROM USER_PROFILE a,USER_PROFILE_GROUP b,MAP_FUNCTION c,FUNCTION_APPLICATION d 
            WHERE a.EMPNO=b.EMPNO AND b.APPLNO=c.APPLNO 
            AND b.GROUPNO=c.GROUPNO AND c.APPLNO=d.APPLNO 
            AND c.FUNCNO=d.FUNCNO AND LOWER(a.EMPNO)= '{0}'
            AND b.APPLNO= '{1}'
            ORDER BY d.SEQMENU,d.SEQSUBMENU1,d.SEQSUBMENU2 
            ", pUsername,pApplNo);

            return dbEngine.GetDataTable(strSql, Conn);
        }

        public DataTable getCurrentDate()
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@"select to_char(sysdate,'dd/mm/yyyy') as c_date from apptoken");

            return dbEngine.GetDataTable(strSql,Conn);
        }
    }
}
